﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.Toook.AutoDownloadHtml.DownloadHtml.ParsePage.Entity;
using Com.Toook.AutoDownloadHtml.Util;
using System.Data.SQLite;
using System.Collections.Specialized;
using System.Collections;
using System.Data;

namespace Com.Toook.AutoDownloadHtml.DownloadHtml.ParsePage
{
    public class SaveResultToDB
    {
        /// <summary>
        /// 保存分类页面中的Url等结果到SiteKindPageUrlTemp***表中(表名在xml在配置)。如果url已经存在返回true, 不存在返回FALSE
        /// </summary>
        /// <param name="result"></param>
        /// <returns>如果url已经存在返回true, 不存在返回FALSE</returns>
        public static bool SaveKindUrl(ResultKind result)
        {
            bool existSameUrl = true;
            

            string command = "insert into " + result.SaveToTableName + "(url, title, summary, img, siteKindId, kindUrl, author, actor, year, area ) "
                + " values(@url, @title, @summary, @img, @siteKindId, @kindUrl, @author, @actor, @year, @area) ";
            SQLiteConnection con = SqliteHelper.GetConnection("SQLiteDatabase");
            SQLiteCommand cmd = new SQLiteCommand(command, con);
            cmd.Parameters.Add("@url", System.Data.DbType.String, 200).Value = result.Url;
            cmd.Parameters.Add("@title", System.Data.DbType.String, 40).Value = result.Title;
            cmd.Parameters.Add("@summary", System.Data.DbType.String).Value = result.Summary;
            cmd.Parameters.Add("@img", System.Data.DbType.String, 200).Value = result.Img;
            cmd.Parameters.Add("@siteKindId", System.Data.DbType.String, 20).Value = result.SiteKindId;
            cmd.Parameters.Add("@kindUrl", System.Data.DbType.String, 200).Value = result.KindPageUrl;
            cmd.Parameters.Add("@author", System.Data.DbType.String, 20).Value = result.Author;
            cmd.Parameters.Add("@actor", System.Data.DbType.String, 20).Value = result.Actor;
            cmd.Parameters.Add("@year", System.Data.DbType.String, 4).Value = result.Year;
            cmd.Parameters.Add("@area", System.Data.DbType.String, 20).Value = result.Area;

            SQLiteCommand existCmd = new SQLiteCommand("select count(*) number from SiteKindPageUrlTemp where url=@url", con);
            existCmd.Parameters.Add("@url", System.Data.DbType.String, 200).Value = result.Url;
            try
            {
                con.Open();
                int number = int.Parse(existCmd.ExecuteScalar().ToString());
                if (number == 0)
                {
                    existSameUrl = false;//不存在相同url的记录
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("SaveKindUrl保存结果错误", ex);
            }
            finally
            {
                SqliteHelper.CloseDbResource(con, null);
            }

            return existSameUrl;
        }
        /// <summary>
        /// 从SiteKindPageUrlTemp表中获取某个分类新增的页面url、标题等
        /// </summary>
        /// <param name="siteKindId"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static DataTable GetKindUrlTemp(string siteKindId, string fields)
        {
            fields = string.IsNullOrEmpty(fields) ? "" : "," + fields;//如果指定从数据库获取字段值，在字段名称前面加上逗号
            string command = "select url" + fields
                + " from SiteKindPageUrlTemp where isGetDetail = 0 and siteKindId='" + siteKindId + "'";
            SQLiteConnection con = SqliteHelper.GetConnection("SQLiteDatabase");
            SQLiteDataAdapter da = new SQLiteDataAdapter(command, con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        /// <summary>
        /// 保存最终内容到数据库
        /// </summary>
        /// <param name="siteKindId">站点分类ID</param>
        /// <param name="tableName">插入表名</param>
        /// <param name="content">内容</param>
        /// <param name="dicResult">其他属性</param>
        public static void SaveFinalContent(string siteKindId, string tableName, string content, StringDictionary dicResult, string startDetailUrl)
        {
            string command = "INSERT INTO " + tableName + " ( ";
            string nameField = "siteKindId";
            string valueField = "@siteKindId";
            string key;
            SQLiteConnection con = SqliteHelper.GetConnection("SQLiteDatabase");
            SQLiteCommand cmd = new SQLiteCommand(con);
            cmd.Parameters.Add("@siteKindId", DbType.String, 20).Value = siteKindId;

            foreach (DictionaryEntry field in dicResult)
            {
                key = field.Key.ToString();
                nameField += "," + key;
                valueField += ",@" + key;

                cmd.Parameters.Add("@" + key, System.Data.DbType.String).Value = field.Value.ToString(); 
            }
            command = command + nameField + ") values (" + valueField + ")";//插入内容
            string updateCmd = "update SiteKindPageUrlTemp set  isGetDetail=1 where url=@url ";//更新Temp,标记为已经获取内容//清空字段title='完', summary=null, img=null, author=null, actor=null,
            cmd.CommandText = command;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                //将SiteKindPageUrlTemp表对应的url记录标记为已经获取内容isGetDetail=1
                cmd.CommandText = updateCmd;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@url", DbType.String, 200).Value = startDetailUrl;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("保存最终内容失败. table=" + tableName, ex);
            }
            finally
            {
                con.Close();
            }
        }
    }
}
